﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MobileAPI.DAL
{
    class DBHelperSQL
    {
        private static string conStr =  ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

        /// <summary>
        /// 执行一条计算查询语句，返回单条记录（object类型）
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static object sqlExecuteScalar(string Sqlstr)
        {
            SqlConnection Con=null;
            try
            {
                Con= new SqlConnection(conStr);
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    Con.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// 执行一条计算查询语句，返回单条记录（object类型）
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static object sqlExecuteScalar(string Sqlstr,List<SqlParameter> list)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    foreach(SqlParameter para in list)
                    {
                        cmd.Parameters.Add(para);
                    }

                    Con.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                Con.Close();
            }
        }


        /// <summary>
        /// 执行一条计算查询语句,只返回影响行数（int类型）
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static int sqlExecuteNonQuery(string Sqlstr)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    Con.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// 执行一条计算查询语句,只返回影响行数（int类型）
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static int sqlExecuteNonQuery(string Sqlstr, List<SqlParameter> list)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    foreach (SqlParameter para in list)
                    {
                      cmd.Parameters.Add(para);
                    }
                    Con.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// 返回数据集（DataSet类型）
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static DataSet ReturnDataSet(string Sqlstr)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                DataSet ds = new DataSet();
                Con.Open();
                SqlDataAdapter cmd = new SqlDataAdapter(Sqlstr, Con);
                cmd.Fill(ds, "ds");
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }


        /// <summary>
        /// 返回List<string>
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="Field">需要获取的字段</param>
        /// <returns></returns>
        public static List<string> ReturnStringList(string Sqlstr, string Field)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                List<string> list = new List<string>();
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    Con.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            string str = Convert.ToString(dr[Field]);
                            list.Add(str);
                        }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// 返回List<string>
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="Field">需要获取的字段</param>
        /// <returns></returns>
        public static List<string> ReturnStringList(string Sqlstr, string Field, List<SqlParameter> plist)
        {
            SqlConnection Con = null;
            try
            {
                Con = new SqlConnection(conStr);
                List<string> list = new List<string>();
                using (SqlCommand cmd = new SqlCommand(Sqlstr, Con))
                {
                    foreach (SqlParameter para in plist)
                    {
                        cmd.Parameters.Add(para);
                    }
                    Con.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            string str = Convert.ToString(dr[Field]);
                            list.Add(str);
                        }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <returns></returns>
        //private static bool Open()
        //{
        //    try
        //    {
        //        _connection.Open();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        //("打开数据库出错:" + e.Message);
        //        return false;
        //    }
        //}

        ///// <summary>
        ///// 关闭连接
        ///// </summary>
        ///// <returns></returns>
        //private static bool Close()
        //{
        //    try
        //    {
        //        _connection.Close();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        //("关闭数据库出错:" + e.Message);
        //        return false;
        //    }
        //}
    }
}

